package com.kcsj.dao.User;


import com.kcsj.domain.user.Role;
import com.kcsj.util.JdbcHelper;

import java.sql.*;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Collection;
//201902104020 李鑫元
public class RoleDao {
    private static final RoleDao roleDao =
            new RoleDao();

    private RoleDao() {

    }

    public static RoleDao getInstance() {
        return roleDao;
    }
    
    public Collection<Role> findAll(Connection conn) throws SQLException {
        ArrayList<Role> roles = new ArrayList<>();
        //预编译方式进行查询
        String sql = "select * from role";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();

        while (resultSet.next()){
            Integer id = resultSet.getInt(1);
            String name = resultSet.getString(2);
            Timestamp timestamp = resultSet.getTimestamp(3);
            String remarks = resultSet.getString(4);
            DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            Role role = new Role(id,name,timestamp.toLocalDateTime().format(dtf),remarks);
            if(!role.getName().equals("超级管理员")){
                roles.add(role);
            }
        }
        JdbcHelper.close(resultSet,preparedStatement);
        return roles;
    }
    
    public Role find(Integer id, Connection conn) throws SQLException {
        Role role = null;
        //预编译方式进行查询
        String s = "select * from role where  id = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setInt(1,id);
        ResultSet resultSet = preparedStatement.executeQuery();
        //将查询到的数据转为Role对象
        if (resultSet.next()){
            String name = resultSet.getString(2);
            Timestamp timestamp = resultSet.getTimestamp(3);
            String remarks = resultSet.getString(4);
            DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            role = new Role(id,name,timestamp.toLocalDateTime().format(dtf),remarks);
        }
        //关闭连接
        JdbcHelper.close(resultSet,preparedStatement);
        return role;
    }

    //修改school表中的数据
    public boolean update(Role role, Connection conn) throws SQLException {
        //预编译方式进行修改数据
        String s = "update role set name = ?,remarks = ? where id = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setString(1,role.getName());
        preparedStatement.setString(2,role.getRemarks());
        preparedStatement.setInt(3,role.getId());
        //如果执行影响行数大于0，则修改成功
        int i = preparedStatement.executeUpdate();
        //关闭连接
        JdbcHelper.close(preparedStatement);
        return i > 0;
    }

    //向Role表中添加数据
    public int add(Role role, Connection conn) throws SQLException {
        //预编译方式进行增加数据
        String s = "insert into role(name,remarks) value (?,?)";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setString(1,role.getName());
        preparedStatement.setString(2,role.getRemarks());
        //如果执行影响行数大于0，则增加成功
        int id = preparedStatement.executeUpdate();
        //关闭连接
        JdbcHelper.close(preparedStatement);
        return id;
    }

    //在school表根据id删除相应数据
    public boolean delete(Integer id, Connection conn) throws SQLException {
        //预编译方式进行删除数据
        String s = "delete from role where id = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setInt(1,id);
        //如果执行影响行数大于0，则增加成功
        int i = preparedStatement.executeUpdate();
        //关闭连接
        JdbcHelper.close(preparedStatement);
        return i > 0;
    }

}
